package com.lambkit.util;

/**
 * @author yangyong(孤竹行)
 */
public class SqlKit {

    public static String replaceSQL(String sql) {
        sql = sql.replaceAll("'", "''");
        sql = sql.replaceAll("/", "//");
        return sql;
    }

    /**
     * @Desc SQL语句效验，防止SQL语句字符注入
     * @param str
     * @return
     */
    public static boolean validateSQL(String str) {
        String strSql = str.toLowerCase();// 统一转为小写
        strSql = strSql.replaceAll("\\.", ",");
        strSql = strSql.replaceAll("  ", " ");
        strSql = strSql.replaceAll(" ", ",");
        strSql = strSql.replaceAll("'", ",");
        strSql = strSql.replaceAll("`", ",");
        strSql = strSql.replaceAll("\"", ",");
        String badStr = "'|and|exec|execute|insert|select|delete|update|count|drop|*|%|chr|mid|master|truncate|"
                + "char|declare|sitename|net user|xp_cmdshell|;|or|-|+|,|like'|and|exec|execute|insert|create|drop|"
                + "table|from|grant|use|group_concat|column_name|"
                + "information_schema.columns|table_schema|union|where|select|delete|update|order|by|count|*|"
                + "chr|mid|master|truncate|char|declare|or|;|-|--|+|,|like|//|/|%|#";// 过滤掉的sql关键字，可以手动添加
        String[] badStrs = badStr.split("\\|");
        for (int i = 0; i < badStrs.length; i++) {
            if (strSql.indexOf("," + badStrs[i] + ",") >= 0) {
                return false;
            }
        }
        return true;
    }

    /**
     * @Desc SQL语句效验，防止SQL语句字符注入
     * @param sSql
     * @return 0 - 没有注入, 1 - 有注入
     */
    public static int filterSQL(String sSql) {
        int srcLen, decLen = 0;
        sSql = sSql.toLowerCase().trim();
        srcLen = sSql.length();
        sSql = sSql.replace("exec", "");
        sSql = sSql.replace("delete", "");
        sSql = sSql.replace("master", "");
        sSql = sSql.replace("truncate", "");
        sSql = sSql.replace("declare", "");
        sSql = sSql.replace("create", "");
        sSql = sSql.replace("xp_", "no");
        decLen = sSql.length();
        if (srcLen == decLen) {
            return 0;
        } else {
            return 1;
        }
    }

    /**
     * @Desc 防止sql注入
     * @param sql
     * @return
     */
    public static String transactSQLInjection(String sql) {
        return sql.replaceAll(".*([';]+|(--)+).*", " ");
    }

    /**
     * @Desc 过滤SQL语句，防止SQL语句字符注入
     * @param inStr
     * @return
     */
    public static String doneSQL(String inStr) {
        StringBuffer sb = new StringBuffer("");
        char[] chStr = inStr.toCharArray();
        for (int j = 0; j < chStr.length; j++) {
            if (chStr[j] == '_' || chStr[j] == '%' || chStr[j] == '\\') {
                sb.append("\\");
            }
            sb.append(chStr[j]);
        }
        return sb.toString();
    }
}
